/*
 * Author: Minh-Tuan Ta
 * Date: 11/6/11
 * Class: UserDB
 * This class used to access the data from User table
 */
package data;

import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.*;
import beans.*;

public class UserDB extends DB {
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/6/11
	 * Method: getUsers
	 * This method will get all the users in the database
	 */
	public static synchronized ArrayList<User> getUsers()
	{
		Connection conn = null;
		ArrayList<User> users = new ArrayList<User>();
		PreparedStatement statement = null;
		String sql = "SELECT userid, username, firstname, lastname, email, password, dob, gender, phone, address1, address2, city, state, zip, lastlogin, picturepath\n" +
					 "FROM user";
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			
			while (rs.next())
			{
				User user = new User();
				setUser(user, rs);
				
				users.add(user);
			}
			
			rs.close();
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return users;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/6/11
	 * Method: getUsers
	 * This method will get the users in the database with the array of userids passed in
	 */
	public static synchronized ArrayList<User> getUsers(ArrayList<Integer> userids)
	{
		Connection conn = null;
		ArrayList<User> users = new ArrayList<User>();
		PreparedStatement statement = null;
		String sql = "SELECT userid, username, firstname, lastname, email, password, dob, gender, phone, address1, address2, city, state, zip, lastlogin, picturepath\n" +
					 "FROM user\n" +
					 "WHERE userid IN (0";
		
		
		for (int i = 0; i < userids.size(); i++)
		{
			sql += ", " + userids.get(i);
		}
		
		sql += ")";
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			
			while (rs.next())
			{
				User user = new User();
				setUser(user, rs);
				
				users.add(user);
			}
			
			rs.close();
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return users;
	}
	
	/*
	 * Author: Keith Westphal
	 * Date: 11/29/11
	 * Method: getUserSearch
	 * this method will return the information about a user when passed a username
	 */
	public static synchronized ArrayList<User> getUserSearch(String searchPhrase)
	{
		Connection conn = null;
		ArrayList<User> users = new ArrayList<User>();
		PreparedStatement statement = null;
		String sql = "SELECT userid, username, firstname, lastname, email, password, dob, gender, phone, address1, address2, city, state, zip, lastlogin, picturepath\n" +
					 "FROM user\n" +
					 "WHERE upper(username) LIKE upper(?)\n" +
					 "OR upper(lastname) LIKE upper(?)\n" +
					 "OR upper(firstname) LIKE upper(?)\n" +
					 "OR upper(email) LIKE upper(?)";

		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setString(1, "%" + searchPhrase + "%");
			statement.setString(2, "%" + searchPhrase + "%");
			statement.setString(3, "%" + searchPhrase + "%");
			statement.setString(4, "%" + searchPhrase + "%");
			ResultSet rs = statement.executeQuery();
			
			while (rs.next())
			{
				User user = new User();
				setUser(user, rs);
				
				users.add(user);
			}
			
			rs.close();
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return users;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/6/11
	 * Method: getUser
	 * This method will get one user from the User table using the userid passed in
	 */
	public static synchronized User getUser(int userid)
	{
		Connection conn = null;
		User user = null;
		PreparedStatement statement = null;
		String sql = "SELECT userid, username, firstname, lastname, email, password, dob, gender, phone, address1, address2, city, state, zip, lastlogin, picturepath\n" +
					 "FROM user\n" +
					 "WHERE userid = ?";
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, userid);
			
			ResultSet rs = statement.executeQuery();
			
			if (rs.next())
			{
				user = new User();
				setUser(user, rs);
			}
			
			rs.close();
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return user;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/6/11
	 * Method: getUser
	 * This method will get one user from the User table with the username given
	 */
	public static synchronized User getUser(String userName)
	{
		Connection conn = null;
		User user = null;
		PreparedStatement statement = null;
		String sql = "SELECT userid, username, firstname, lastname, email, password, dob, gender, phone, address1, address2, city, state, zip, lastlogin, picturepath\n" +
					 "FROM user\n" +
					 "WHERE UPPER(username) = UPPER(?)";
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setString(1, userName);
			
			ResultSet rs = statement.executeQuery();
			
			if (rs.next())
			{
				user = new User();
				setUser(user, rs);
			}
			
			rs.close();
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return user;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/6/11
	 * Method: deleteUser
	 * This method will delete a user using userid
	 */
	public static synchronized int deleteUser(int userid)
	{
		Connection conn = null;
		int status = 0;
		PreparedStatement statement = null;
		String sql = "DELETE FROM user\n" +
					 "WHERE userid = ?";
		
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, userid);
			status = statement.executeUpdate();
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return status;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/6/11
	 * Method: updateUser
	 * This method will update a user using User object
	 */
	public static synchronized int updateUser(User user)
	{
		Connection conn = null;
		int status = 0;
		PreparedStatement statement = null;
		String sql = "UPDATE user\n" +
					 "SET username = ?, firstname = ?, lastname = ?, email = ?, password = ?, dob = ?" +
					 ",gender = ?, phone = ?, address1 = ?, address2 = ?, city = ?, state = ?, zip = ?" +
					 ",lastlogin = ?, picturepath = ?\n" +
					 "WHERE userid = ?";
		
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setString(1, user.getUserName());
			statement.setString(2, user.getFirstName());
			statement.setString(3, user.getLastName());
			statement.setString(4, user.getEmail());
			statement.setString(5, user.getPassword());
			statement.setDate(6, user.getDob());
			statement.setBoolean(7, user.isGender());
			statement.setString(8, user.getPhone());
			statement.setString(9, user.getAddress1());
			statement.setString(10, user.getAddress2());
			statement.setString(11, user.getCity());
			statement.setString(12, user.getState());
			statement.setString(13, user.getZip());
			statement.setTimestamp(14, user.getLastlogin());
			statement.setString(15, user.getPicturePath());
			statement.setInt(16, user.getUserID());
			
			status = statement.executeUpdate();
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return status;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/6/11
	 * Method: addUser
	 * This method will add a new user using User object
	 */
	public static synchronized int addUser(User user)
	{
		Connection conn = null;
		int status = 0;
		PreparedStatement statement = null;
		String sql = "INSERT INTO user(username, firstname, lastname, email, password, dob, gender, phone, address1, address2, city, state, zip, lastlogin, picturepath)\n" +
					 "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setString(1, user.getUserName());
			statement.setString(2, user.getFirstName());
			statement.setString(3, user.getLastName());
			statement.setString(4, user.getEmail());
			statement.setString(5, user.getPassword());
			statement.setDate(6, user.getDob());
			statement.setBoolean(7, user.isGender());
			statement.setString(8, user.getPhone());
			statement.setString(9, user.getAddress1());
			statement.setString(10, user.getAddress2());
			statement.setString(11, user.getCity());
			statement.setString(12, user.getState());
			statement.setString(13, user.getZip());
			statement.setTimestamp(14, user.getLastlogin());
			statement.setString(15, user.getPicturePath());
			
			status = statement.executeUpdate();
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return status;
	}
}
